﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MODEL;
using MySql.Data.MySqlClient;
using Dapper;

namespace DAL
{
    public class AreaWeatherPictureDAL
    {
        public static Task<IEnumerable<AreaWeatherPicture>> QueryAreaWeatherPictures(int? townId)
        {
            string sql = @"SELECT A.WeatherPicId
                        , A.Weather
                        , A.Temperature
                        , A.AirQuantity
                        , A.Precipitation
                        , A.PictureURL
                        , A.Content
                        , A.description
						, T.TownId
                        , T.TownName
                        FROM msap_db.area_weather_picture A
                        INNER JOIN msap_db.area_town T
                        ON A.TownId = T.TownId
                        WHERE
                        (@TownId IS NULL OR A.TownId = @TownId)";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.QueryAsync<AreaWeatherPicture, Town, AreaWeatherPicture>(sql, (areaWeatherPicture,town) => {
                    areaWeatherPicture.Town = town;
                    return areaWeatherPicture;                       
                    },
                    new
                    {
                        TownId = townId,                     
                    },splitOn: "TownId");
            }
        }

        public static Task<int> DeleteAreaWeatherPicture(List<int> weatherIds)
        {
            string sql = $"DELETE FROM msap_db.area_weather_picture WHERE WeatherPicId IN({string.Join(",", weatherIds)})";

            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.ExecuteAsync(sql);
            }
        }

        public static Task<int> InsertAreaWeatherPicture(AreaWeatherPicture areaWeatherPicture)
        {
            string sql = @"CALL SP_InsertAreaWeatherPicture(@TownId,@Weather,@Temperature,@AirQuantity,@Precipitation,@PictureUrl,@Content,@description,@UserId)";
            using (MySqlConnection conn = new MySqlConnection(AppSetting.Instance.MySqlConnectionStr))
            {
                return conn.ExecuteAsync(sql, new
                {
                    TownId = areaWeatherPicture.Town.TownId,
                    Weather = areaWeatherPicture.Weather,
                    Temperature = areaWeatherPicture.Temperature,
                    AirQuantity = areaWeatherPicture.AirQuantity,
                    Precipitation = areaWeatherPicture.Precipitation,
                    PictureUrl = areaWeatherPicture.PictureURL,
                    Content = areaWeatherPicture.Content,
                    description = areaWeatherPicture.description,
                    UserId = AppSetting.Instance.User.UserId
                });
            }
        }
    }
}
